package com.vogue.supplymall.order.domain.provider;

import com.vogue.ssm.database.provider.BaseSqlProvider;
import com.vogue.ssm.database.util.DealStatus;
import com.vogue.supplymall.common.domain.dto.OrderInfo;

import java.util.Arrays;

/**
 * The type Order sql provider.
 *
 * @aouthor: zhangliang
 * @date: Create in 2018-01-24
 */
public class OrderSqlProvider extends BaseSqlProvider {

    /**
     * Find string.
     *
     * @param orderInfo the order info
     * @return the string
     */
    public String find(OrderInfo orderInfo) {
        StringBuilder sql = new StringBuilder();
        sql.append(" SELECT ")
                .append(" o.id oid, ")
                .append(" oi.id oiid, ")
                .append(" oip.id oipid, ")
                .append(" s.id sid, ")
                .append(" s.`name` sname, ")
                .append(" s.entname, ")
                // 20180806 添加查询机构id和syscodeid
                .append(" s.syscodeid sellsyscodeid, ")
                .append(" s.organid sellorganid, ")
                /**添加支付所需的mcid*/
                .append(" s.mcid, ")
                /**添加支付所需的mcid*/
                .append(" s.disabledflag s_invalid, ")
                .append(" s.sendamount, ")
                .append(" p.id pid, ")
                .append(" p.`name` pname, ")
                .append(" p.matchid pmatchid, ")//查询出商品的matchid
                .append(" p.disabledflag p_invalid, ")
                /*运费计算相关 start*/
                .append(" s.freight s_freight, ")
                .append(" s.freightamount s_freightamount, ")
                .append(" s.freightstep s_freightstep, ")
                .append(" s.freightstepamount s_freightstepamount, ")
                .append(" p.freight p_freight, ")
                .append(" p.freightamount p_freightamount, ")
                .append(" p.freightstep p_freightstep, ")
                .append(" p.freightstepamount p_freightstepamount, ")
                .append(" p.freighttype p_freighttype, ")
                /*运费计算相关 end*/
                .append(" IF(p.disabledflag = 0,IF(rpc.syscodeid is null,if(p.hiddenflag = 1,1,0),0),1) invalid, ")
                .append(" p.sendlvl, ")
                .append(" IF ( p.sendlvl = 1, 'all',  IF ( p.sendlvl = 2, s.area,  IF ( p.sendlvl = 4, s.province,  IF (p.sendlvl = 8, s.city, 0) ) ) ) area, ")
                .append(" oip.attrplanid, ")
                .append(" pa.inventory, ")
                .append(" pa.weight, ")
                .append(" pp1.price public_price, ")
                .append(" pav.`name` unit_name, ")
                .append(" pav.matchid unit_matchid, ")//查询出品项单位的matchid
                .append(" p.hiddenflag, ")
                .append(" pp2.price private_price, ")
                .append(" oip.createdate, ")
                .append(" pi.url imgurl, ")
                .append(" oi.storageid, ")
                .append(" oi.`storage`, ")
                .append(" oi.itemquantity ");
        sql.append(" FROM ")
                .append(" order_buyer ob ")
                .append(" INNER JOIN `order` o ON (o.id = ob.orderid) ")
                .append(" INNER JOIN order_item oi ON (oi.orderid = o.id) ")
                .append(" INNER JOIN order_item_product oip ON (oip.orderitemid = oi.id) ")
                .append(" INNER JOIN product p ON (oip.productid = p.id) ")
                .append(" INNER JOIN shop s ON (p.shopid = s.id) ")
                .append(" INNER JOIN product_img pi on (pi.productid = p.id and listflag = 1) ")
                .append(" INNER JOIN product_attrplan pa ON (p.id = pa.productid AND pa.id = oip.attrplanid and pa.disabledflag = 0 ) ")
                .append(" INNER JOIN product_attrplan_detail pad ON (pad.attrplanid = pa.id) ")
                .append(" INNER JOIN product_attrvalue pav ON ( ")
                .append(" pav.id = pad.attrvalueid ")
                .append(" AND pav.typeid = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN rel_product_code rpc ON ( ")
                .append(" p.id = rpc.productid ")
                .append(" AND rpc.syscodeid = ob.syscodeid ")
                .append(" ) ")
                .append(" INNER JOIN product_price pp1 ON ( ")
                .append(" p.id = pp1.productid ")
                .append(" AND pa.id = pp1.attrplanid ")
                .append(" AND pp1.allflag = 1 ")
                .append(" ) ")
                .append(" LEFT JOIN product_price pp2 ON ( ")
                .append(" p.id = pp2.productid ")
                .append(" AND pa.id = pp2.attrplanid ")
                .append(" AND pp2.allflag = 0 ")
                .append(" AND pp2.oneid = ob.syscodeid ")
                // 2018/2/26 匹配时间区间允许的私有价格
                .append(" AND pp2.begindate < NOW() ")
                .append(" AND pp2.enddate > NOW() ")
                .append(" ) ");
        sql.append(" WHERE ")
                .append(" o.id = #{oid} ")
                .append(" and oi.id in ( ");
        Arrays.stream(orderInfo.getOiids()).map(s -> s + ",").forEach(sql::append);
        sql.append(" 0 ) ");
        sql.append(" and oip.id in ( ");
        Arrays.stream(orderInfo.getOipids()).map(s -> s + ",").forEach(sql::append);
        sql.append(" 0 ) ")
                .append(" GROUP BY ")
                .append(" oi.id ");
        return DealStatus.formatShadeSql(sql.toString());
    }
}
